<!DOCTYPE html>
<html>
<head>
  <meta charset="utf-8">
  
  <meta name="google-site-verification" content="k9iQUbEI9rWq3xYeh63ATztKdkthC4dNRHV_25maJ3Q" />
  <title>mysql主从复制原理理解与实践 | Taylor&#39;s Learning Diary</title>
  <meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1">
  <meta name="description" content="只从最简单的mysql主从复制原理与实践着手，之前看到Yshiba老师讲解的挺细致透彻，顺便做下笔记并实践一下。 主从复制原理 replication主服务器(master)的binlog,记录master的各种操作（insert、update、delete），master的磁盘只要发生变化就会记录到自己的binlog里面。 从服务器（slave）如果能读取到master的binlog,那么就可以">
<meta name="keywords" content="mysql">
<meta property="og:type" content="article">
<meta property="og:title" content="mysql主从复制原理理解与实践">
<meta property="og:url" content="https://upeng.github.io/blog/2016/12/05/mysql-master-slave/index.html">
<meta property="og:site_name" content="Taylor&#39;s Learning Diary">
<meta property="og:description" content="只从最简单的mysql主从复制原理与实践着手，之前看到Yshiba老师讲解的挺细致透彻，顺便做下笔记并实践一下。 主从复制原理 replication主服务器(master)的binlog,记录master的各种操作（insert、update、delete），master的磁盘只要发生变化就会记录到自己的binlog里面。 从服务器（slave）如果能读取到master的binlog,那么就可以">
<meta property="og:locale" content="zh-CN">
<meta property="og:image" content="https://raw.githubusercontent.com/upeng/upeng.github.io/master/image/mysql-master-slave.gif">
<meta property="og:updated_time" content="2017-09-28T14:46:04.609Z">
<meta name="twitter:card" content="summary">
<meta name="twitter:title" content="mysql主从复制原理理解与实践">
<meta name="twitter:description" content="只从最简单的mysql主从复制原理与实践着手，之前看到Yshiba老师讲解的挺细致透彻，顺便做下笔记并实践一下。 主从复制原理 replication主服务器(master)的binlog,记录master的各种操作（insert、update、delete），master的磁盘只要发生变化就会记录到自己的binlog里面。 从服务器（slave）如果能读取到master的binlog,那么就可以">
<meta name="twitter:image" content="https://raw.githubusercontent.com/upeng/upeng.github.io/master/image/mysql-master-slave.gif">
  
    <link rel="alternate" href="/atom.xml" title="Taylor&#39;s Learning Diary" type="application/atom+xml">
  
  
    <link rel="icon" href="/favicon.png">
  
  
    <link href="//fonts.googleapis.com/css?family=Source+Code+Pro" rel="stylesheet" type="text/css">
  
  <link rel="stylesheet" href="/blog/css/style.css">
  

</head>

<body>
  <div id="container">
    <div id="wrap">
      <header id="header">
  <div id="banner"></div>
  <div id="header-outer" class="outer">
    <div id="header-title" class="inner">
      <h1 id="logo-wrap">
        <a href="/blog/" id="logo">Taylor&#39;s Learning Diary</a>
      </h1>
      
    </div>
    <div id="header-inner" class="inner">
      <nav id="main-nav">
        <a id="main-nav-toggle" class="nav-icon"></a>
        
          <a class="main-nav-link" href="/blog/">Home</a>
        
          <a class="main-nav-link" href="/blog/archives">Archives</a>
        
      </nav>
      <nav id="sub-nav">
        
          <a id="nav-rss-link" class="nav-icon" href="/atom.xml" title="RSS Feed"></a>
        
        <a id="nav-search-btn" class="nav-icon" title="搜索"></a>
      </nav>
      <div id="search-form-wrap">
        <form action="//google.com/search" method="get" accept-charset="UTF-8" class="search-form"><input type="search" name="q" class="search-form-input" placeholder="Search"><button type="submit" class="search-form-submit">&#xF002;</button><input type="hidden" name="sitesearch" value="https://upeng.github.io/blog"></form>
      </div>
    </div>
  </div>
</header>
      <div class="outer">
        <section id="main"><article id="post-mysql-master-slave" class="article article-type-post" itemscope itemprop="blogPost">
  <div class="article-meta">
    <a href="/blog/2016/12/05/mysql-master-slave/" class="article-date">
  <time datetime="2016-12-05T14:40:03.000Z" itemprop="datePublished">2016-12-05</time>
</a>
    
  <div class="article-category">
    <a class="article-category-link" href="/blog/categories/MySQL/">MySQL</a>
  </div>

  </div>
  <div class="article-inner">
    
    
      <header class="article-header">
        
  
    <h1 class="article-title" itemprop="name">
      mysql主从复制原理理解与实践
    </h1>
  

      </header>
    
    <div class="article-entry" itemprop="articleBody">
      
        <p>只从最简单的mysql主从复制原理与实践着手，之前看到Yshiba老师讲解的挺细致透彻，顺便做下笔记并实践一下。</p>
<h2 id="主从复制原理-replication"><a href="#主从复制原理-replication" class="headerlink" title="主从复制原理 replication"></a><a href="#主从复制原理-replication" title="主从复制原理 replication"></a>主从复制原理 replication</h2><p>主服务器(master)的binlog,记录master的各种操作（insert、update、delete），master的磁盘只要发生变化就会记录到自己的binlog里面。</p>
<p>从服务器（slave）如果能读取到master的binlog,那么就可以将master的所做操作跟着执行一遍。但是由于master的binlog中含有master的一些信息，所以需要对binlog进行分析加工，slave的relaylog就是干这种事的，实际上slave将使用relaylog终极日志上的sql操作，保证主从数据一致性。<br><a id="more"></a><br><img src="https://raw.githubusercontent.com/upeng/upeng.github.io/master/image/mysql-master-slave.gif" alt="mysql-master-slave"><br><a id="more"></a><br><strong>总结</strong>：</p>
<ol>
<li>master要配置binlog</li>
<li>slave要配置relaylog</li>
<li>master需要grant slave账号使得slave有权读取master的binlog</li>
<li>slave使用账号连接master</li>
</ol>
<h2 id="主从配置过程"><a href="#主从配置过程" class="headerlink" title="主从配置过程"></a><a href="#主从配置过程" title="主从配置过程"></a>主从配置过程</h2><h3 id="编辑master的配置文件my-cnf"><a href="#编辑master的配置文件my-cnf" class="headerlink" title="编辑master的配置文件my.cnf"></a><a href="#编辑master的配置文件my-cnf" title="编辑master的配置文件my.cnf"></a>编辑master的配置文件my.cnf</h3><figure class="highlight ini"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div></pre></td><td class="code"><pre><div class="line"><span class="comment">#1.配置server-id</span></div><div class="line"><span class="attr">server-id</span>=<span class="number">101</span></div><div class="line"></div><div class="line"><span class="comment">#2.开启二进制日志</span></div><div class="line"><span class="attr">log-bin</span>=mysql-bin</div><div class="line"></div><div class="line"><span class="comment">#3.指定二进制日志格式</span></div><div class="line"><span class="attr">binlog-format</span>=mixed</div></pre></td></tr></table></figure>
<blockquote>
<p>二进制日志格式binlog-format</p>
</blockquote>
<ol>
<li>statement：二进制记录的是执行语句，update insert…</li>
<li>row：二进制执行的是磁盘变化</li>
<li>mixed：混合的，由系统根据语句决定</li>
</ol>
<blockquote>
<p>到底使用哪种format，根据实际情况判断</p>
</blockquote>
<p><strong>比如：</strong><br>磁盘变化少，语句长，使用row<br><code>update user set age = age + 1 where id = 2;</code><br>磁盘变化多，语句短，使用statement<br><code>update slary = slary + 1000;</code> (10w条数据)</p>
<h3 id="编辑slave的配置文件my-cnf"><a href="#编辑slave的配置文件my-cnf" class="headerlink" title="编辑slave的配置文件my.cnf"></a><a href="#编辑slave的配置文件my-cnf" title="编辑slave的配置文件my.cnf"></a>编辑slave的配置文件my.cnf</h3><figure class="highlight ini"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div><div class="line">7</div><div class="line">8</div></pre></td><td class="code"><pre><div class="line"><span class="comment">#1.配置server-id</span></div><div class="line"><span class="attr">server-id</span>=<span class="number">102</span></div><div class="line"></div><div class="line"><span class="comment">#2.开启二进制日志(备份)</span></div><div class="line"><span class="attr">log-bin</span>=mysql-bin</div><div class="line"></div><div class="line"><span class="comment">#3.创建relay日志</span></div><div class="line"><span class="attr">relay-log</span>=mysql-relay</div></pre></td></tr></table></figure>
<blockquote>
<p>配置完成后重启两个机器的mysql服务器<br><code>service mysqld restart</code></p>
</blockquote>
<h2 id="启动主从建立关系"><a href="#启动主从建立关系" class="headerlink" title="启动主从建立关系"></a><a href="#启动主从建立关系" title="启动主从建立关系"></a>启动主从建立关系</h2><h3 id="master的操作"><a href="#master的操作" class="headerlink" title="master的操作"></a><a href="#master的操作" title="master的操作"></a>master的操作</h3><p>查看主服务器的状态<br><code>show master status</code></p>
<figure class="highlight livescript"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div></pre></td><td class="code"><pre><div class="line"><span class="comment">#授权账号密码</span></div><div class="line">grant replication clinet,replication slave <span class="literal">on</span> *.* <span class="keyword">to</span> test@<span class="string">'192.168.1.%'</span> identified <span class="keyword">by</span> <span class="string">'123qwe'</span>;</div><div class="line"></div><div class="line">flush privileges;</div></pre></td></tr></table></figure>
<h3 id="slave的操作"><a href="#slave的操作" class="headerlink" title="slave的操作"></a><a href="#slave的操作" title="slave的操作"></a>slave的操作</h3><p>建立连接</p>
<figure class="highlight routeros"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div><div class="line">4</div><div class="line">5</div><div class="line">6</div></pre></td><td class="code"><pre><div class="line">change master <span class="keyword">to</span></div><div class="line"><span class="attribute">master_host</span>=<span class="string">'192.168.1.101'</span></div><div class="line"><span class="attribute">master_user</span>=<span class="string">'test'</span></div><div class="line"><span class="attribute">master_password</span>=<span class="string">'123qwe'</span></div><div class="line"><span class="attribute">master_log_file</span>=<span class="string">'mysql-bin.000001'</span></div><div class="line"><span class="attribute">master_log_pass</span>=348</div></pre></td></tr></table></figure>
<p>查看连接状态<br><code>show slave status</code></p>
<p>启动从服务器同步进程并查看状态<br><code>start slave</code></p>
<blockquote>
<p>如果报错Last_IO_Error: error connect to master ‘test@192.168.1.101:3306’ ……<br>使用Telnet 192.168.1.101 3306排查连接master是否成功<br>有可能是防火墙开了，关闭防火墙：<code>services iptables stop</code></p>
</blockquote>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><div class="line">1</div><div class="line">2</div><div class="line">3</div></pre></td><td class="code"><pre><div class="line"><span class="keyword">stop</span> <span class="keyword">slave</span>;</div><div class="line"><span class="keyword">start</span> <span class="keyword">slave</span>;</div><div class="line"><span class="keyword">show</span> <span class="keyword">slave</span> <span class="keyword">status</span>;</div></pre></td></tr></table></figure>
<h3 id="验证测试"><a href="#验证测试" class="headerlink" title="验证测试"></a><a href="#验证测试" title="验证测试"></a>验证测试</h3><p>在mater中创建数据库和表，并做各种增删改操作，到从库观察验证</p>
<h2 id="补充说明"><a href="#补充说明" class="headerlink" title="补充说明"></a><a href="#补充说明" title="补充说明"></a>补充说明</h2><p><strong>mysql经常报错如下：</strong><br><code>can&#39;t connect to local mysql server through socket &#39;/tmp/mysqld.sock&#39;</code><br>通过<code>/tmp/mysqld.sock</code>下的socket文件连接不成功，比如我们默认安装的mysql通过配置文件可以发现mysql.sock在<code>/var/lib/mysql/mysql.sock</code>下。也就是说/tmp/下没有mysqld.sock文件</p>
<p><strong>有两种解决方法：</strong></p>
<ol>
<li>指定sock文件位置<br><code>mysql -uroot -p -S /var/lib/mysql/mysql.sock</code></li>
<li>建立一个软连接<br><code>ln -s /var/lib/mysql/mysql.sock /tmp/mysqld.sock</code></li>
</ol>
<h2 id="后续学习计划"><a href="#后续学习计划" class="headerlink" title="后续学习计划"></a><a href="#后续学习计划" title="后续学习计划"></a>后续学习计划</h2><p>主从复制可能会出现的问题级解决方案<br>mysql读写分离策略</p>

      
    </div>
    <footer class="article-footer">
      <a data-url="https://upeng.github.io/blog/2016/12/05/mysql-master-slave/" data-id="cj8x7817v001igwp4e95v18cm" class="article-share-link">Share</a>
      
        <a href="https://upeng.github.io/blog/2016/12/05/mysql-master-slave/#disqus_thread" class="article-comment-link">留言</a>
      
      
  <ul class="article-tag-list"><li class="article-tag-list-item"><a class="article-tag-list-link" href="/blog/tags/mysql/">mysql</a></li></ul>

    </footer>
  </div>
  
    
<nav id="article-nav">
  
    <a href="/blog/2016/12/08/idempotence/" id="article-nav-newer" class="article-nav-link-wrap">
      <strong class="article-nav-caption">Newer</strong>
      <div class="article-nav-title">
        
          在编程中的幂等性idempotence
        
      </div>
    </a>
  
  
    <a href="/blog/2016/12/04/zsh-and-item2/" id="article-nav-older" class="article-nav-link-wrap">
      <strong class="article-nav-caption">Older</strong>
      <div class="article-nav-title">Mac下的终端和命令行item2和zsh</div>
    </a>
  
</nav>

  
</article>


<section id="comments">
  <div id="disqus_thread">
    <noscript>Please enable JavaScript to view the <a href="//disqus.com/?ref_noscript">comments powered by Disqus.</a></noscript>
  </div>
</section>
</section>
        
          <aside id="sidebar">
  
    
  <div class="widget-wrap">
    <h3 class="widget-title">分类</h3>
    <div class="widget">
      <ul class="category-list"><li class="category-list-item"><a class="category-list-link" href="/blog/categories/Linux/">Linux</a><span class="category-list-count">8</span></li><li class="category-list-item"><a class="category-list-link" href="/blog/categories/MySQL/">MySQL</a><span class="category-list-count">6</span></li><li class="category-list-item"><a class="category-list-link" href="/blog/categories/PHP/">PHP</a><span class="category-list-count">15</span></li><li class="category-list-item"><a class="category-list-link" href="/blog/categories/Thinking/">Thinking</a><span class="category-list-count">1</span></li><li class="category-list-item"><a class="category-list-link" href="/blog/categories/其他/">其他</a><span class="category-list-count">8</span></li><li class="category-list-item"><a class="category-list-link" href="/blog/categories/前端/">前端</a><span class="category-list-count">2</span></li><li class="category-list-item"><a class="category-list-link" href="/blog/categories/数据库范式/">数据库范式</a><span class="category-list-count">1</span></li></ul>
    </div>
  </div>


  
    
  <div class="widget-wrap">
    <h3 class="widget-title">标签云</h3>
    <div class="widget tagcloud">
      <a href="/blog/tags/CI/" style="font-size: 10px;">CI</a> <a href="/blog/tags/JQuery/" style="font-size: 10px;">JQuery</a> <a href="/blog/tags/Mac/" style="font-size: 13.33px;">Mac</a> <a href="/blog/tags/MySQL/" style="font-size: 10px;">MySQL</a> <a href="/blog/tags/awk/" style="font-size: 10px;">awk</a> <a href="/blog/tags/bash/" style="font-size: 10px;">bash</a> <a href="/blog/tags/composer/" style="font-size: 10px;">composer</a> <a href="/blog/tags/eloquent/" style="font-size: 10px;">eloquent</a> <a href="/blog/tags/hexo/" style="font-size: 10px;">hexo</a> <a href="/blog/tags/idempotence/" style="font-size: 10px;">idempotence</a> <a href="/blog/tags/item/" style="font-size: 10px;">item</a> <a href="/blog/tags/laravel/" style="font-size: 20px;">laravel</a> <a href="/blog/tags/linux/" style="font-size: 16.67px;">linux</a> <a href="/blog/tags/mac/" style="font-size: 10px;">mac</a> <a href="/blog/tags/memcacheq/" style="font-size: 10px;">memcacheq</a> <a href="/blog/tags/mysql/" style="font-size: 16.67px;">mysql</a> <a href="/blog/tags/nc/" style="font-size: 10px;">nc</a> <a href="/blog/tags/packageist/" style="font-size: 10px;">packageist</a> <a href="/blog/tags/php/" style="font-size: 13.33px;">php</a> <a href="/blog/tags/sed/" style="font-size: 10px;">sed</a> <a href="/blog/tags/shell/" style="font-size: 10px;">shell</a> <a href="/blog/tags/static/" style="font-size: 10px;">static</a> <a href="/blog/tags/thinking/" style="font-size: 13.33px;">thinking</a> <a href="/blog/tags/tmux/" style="font-size: 10px;">tmux</a> <a href="/blog/tags/vagrant/" style="font-size: 10px;">vagrant</a> <a href="/blog/tags/validator/" style="font-size: 10px;">validator</a> <a href="/blog/tags/vim/" style="font-size: 10px;">vim</a> <a href="/blog/tags/vuejs/" style="font-size: 10px;">vuejs</a> <a href="/blog/tags/zephir/" style="font-size: 10px;">zephir</a> <a href="/blog/tags/zsh/" style="font-size: 10px;">zsh</a> <a href="/blog/tags/设计模式/" style="font-size: 13.33px;">设计模式</a>
    </div>
  </div>

  
    
  <div class="widget-wrap">
    <h3 class="widget-title">归档</h3>
    <div class="widget">
      <ul class="archive-list"><li class="archive-list-item"><a class="archive-list-link" href="/blog/archives/2017/10/">十月 2017</a><span class="archive-list-count">1</span></li><li class="archive-list-item"><a class="archive-list-link" href="/blog/archives/2017/09/">九月 2017</a><span class="archive-list-count">4</span></li><li class="archive-list-item"><a class="archive-list-link" href="/blog/archives/2016/12/">十二月 2016</a><span class="archive-list-count">4</span></li><li class="archive-list-item"><a class="archive-list-link" href="/blog/archives/2016/11/">十一月 2016</a><span class="archive-list-count">1</span></li><li class="archive-list-item"><a class="archive-list-link" href="/blog/archives/2016/10/">十月 2016</a><span class="archive-list-count">2</span></li><li class="archive-list-item"><a class="archive-list-link" href="/blog/archives/2016/09/">九月 2016</a><span class="archive-list-count">1</span></li><li class="archive-list-item"><a class="archive-list-link" href="/blog/archives/2016/08/">八月 2016</a><span class="archive-list-count">3</span></li><li class="archive-list-item"><a class="archive-list-link" href="/blog/archives/2016/07/">七月 2016</a><span class="archive-list-count">2</span></li><li class="archive-list-item"><a class="archive-list-link" href="/blog/archives/2016/06/">六月 2016</a><span class="archive-list-count">3</span></li><li class="archive-list-item"><a class="archive-list-link" href="/blog/archives/2016/05/">五月 2016</a><span class="archive-list-count">4</span></li><li class="archive-list-item"><a class="archive-list-link" href="/blog/archives/2016/04/">四月 2016</a><span class="archive-list-count">7</span></li><li class="archive-list-item"><a class="archive-list-link" href="/blog/archives/2016/02/">二月 2016</a><span class="archive-list-count">1</span></li><li class="archive-list-item"><a class="archive-list-link" href="/blog/archives/2016/01/">一月 2016</a><span class="archive-list-count">2</span></li><li class="archive-list-item"><a class="archive-list-link" href="/blog/archives/2015/10/">十月 2015</a><span class="archive-list-count">3</span></li><li class="archive-list-item"><a class="archive-list-link" href="/blog/archives/2015/08/">八月 2015</a><span class="archive-list-count">2</span></li><li class="archive-list-item"><a class="archive-list-link" href="/blog/archives/2015/07/">七月 2015</a><span class="archive-list-count">1</span></li></ul>
    </div>
  </div>


  
    
  <div class="widget-wrap">
    <h3 class="widget-title">最新文章</h3>
    <div class="widget">
      <ul>
        
          <li>
            <a href="/blog/2017/10/18/laravel-validator/">Laravel Validator自定义参数验证规则</a>
          </li>
        
          <li>
            <a href="/blog/2017/09/28/linux-sed/">linux常用命令之sed</a>
          </li>
        
          <li>
            <a href="/blog/2017/09/27/shell-script-learning/">shell脚本由点到面学习总结</a>
          </li>
        
          <li>
            <a href="/blog/2017/09/14/laravel-eloquent-index/">Eloquent ORM多个and和or条件查询</a>
          </li>
        
          <li>
            <a href="/blog/2017/09/10/Linux压缩解压缩命令-index/">Linux常用压缩解压缩命令</a>
          </li>
        
      </ul>
    </div>
  </div>

  
</aside>
        
      </div>
      <footer id="footer">
  
  <div class="outer">
    <div id="footer-info" class="inner">
      &copy; 2017 Tayloryu<br>
      Powered by <a href="http://hexo.io/" target="_blank">Hexo</a>
    </div>
  </div>
</footer>
    </div>
    <nav id="mobile-nav">
  
    <a href="/blog/" class="mobile-nav-link">Home</a>
  
    <a href="/blog/archives" class="mobile-nav-link">Archives</a>
  
</nav>
    
<script>
  var disqus_shortname = 'tayloryu';
  
  var disqus_url = 'https://upeng.github.io/blog/2016/12/05/mysql-master-slave/';
  
  (function(){
    var dsq = document.createElement('script');
    dsq.type = 'text/javascript';
    dsq.async = true;
    dsq.src = '//' + disqus_shortname + '.disqus.com/embed.js';
    (document.getElementsByTagName('head')[0] || document.getElementsByTagName('body')[0]).appendChild(dsq);
  })();
</script>


<script src="//ajax.googleapis.com/ajax/libs/jquery/2.0.3/jquery.min.js"></script>


  <link rel="stylesheet" href="/blog/fancybox/jquery.fancybox.css">
  <script src="/blog/fancybox/jquery.fancybox.pack.js"></script>


<script src="/blog/js/script.js"></script>
  </div>
</body>
</html>